﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using ET.DBCommon.Model;
using System.Collections.ObjectModel;
using MySql.Data.MySqlClient;

namespace ET.DBCommon.DAL
{
    public class PaperDAL
    {
        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="newId"></param>
        /// <param name="paper"></param>
        /// <returns></returns>
        public static bool InsertTestPaper(out int newId, PaperModel paper)
        {
            string sql = "insert into paper (`Describes`,TesQuestiontList,CreateTime,UpdateTime,TotalScore,Level) values (@Describes,@TesQuestiontList,now(),now(),@TotalScore,@Level)";
            MySqlParameter[] parms = {
                new MySqlParameter("@Describes", paper.Describes),
                new MySqlParameter("@TesQuestiontList",paper.TesQuestiontList),
                new MySqlParameter("@TotalScore",paper.TotalScore),
                new MySqlParameter("@Level",paper.Level)
            };

            return DbHelper.ExecuteNonQuery(out newId, sql, parms) > 0;
        }

        public static List<PaperModel> SelectTestPaperList(bool ContainQuestion = true)
        {
            string sql = "select * from paper";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            List<PaperModel> lst = new List<PaperModel>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    PaperModel info = new PaperModel();
                    info.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
                    info.Describes = dt.Rows[i]["Describes"].ToString();
                    info.Level = dt.Rows[i]["Level"].ToString();
                    info.TesQuestiontList = dt.Rows[i]["TesQuestiontList"].ToString();
                    if (!(dt.Rows[i]["TotalScore"] is DBNull))
                        info.TotalScore =decimal.Parse(dt.Rows[i]["TotalScore"].ToString());
                    if (!(dt.Rows[i]["CreateTime"] is DBNull))
                        info.CreateTime = DateTime.Parse(dt.Rows[i]["CreateTime"].ToString());
                    if (!(dt.Rows[i]["UpdateTime"] is DBNull))
                        info.UpdateTime = DateTime.Parse(dt.Rows[i]["UpdateTime"].ToString());
                    lst.Add(info);
                }
            }
            return lst;
        }

        public static List<PaperModel> SelectTestPaperList()
        {
            string sql = $"select * from paper";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            List<PaperModel> lst = new List<PaperModel>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    PaperModel info = new PaperModel();
                    info.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
                    info.Describes = dt.Rows[i]["Describes"].ToString();
                    info.Level = dt.Rows[i]["Level"].ToString();
                    info.TesQuestiontList = dt.Rows[i]["TesQuestiontList"].ToString();
                    if (!(dt.Rows[i]["TotalScore"] is DBNull))
                        info.TotalScore = decimal.Parse(dt.Rows[i]["TotalScore"].ToString());
                    if (!(dt.Rows[i]["CreateTime"] is DBNull))
                        info.CreateTime = DateTime.Parse(dt.Rows[i]["CreateTime"].ToString());
                    if (!(dt.Rows[i]["UpdateTime"] is DBNull))
                        info.UpdateTime = DateTime.Parse(dt.Rows[i]["UpdateTime"].ToString());
                    lst.Add(info);
                }
            }
            return lst;
        }

        public static bool UpdateTestPaper(PaperModel paper)
        {
            string sql = "update paper set `Describes` = @Describes,TesQuestiontList = @TesQuestiontList,UpdateTime = @UpdateTime,Level=@Level WHERE Id = @Id";
            MySqlParameter[] parms = {
                new MySqlParameter("@Describes", paper.Describes),
                new MySqlParameter("@Id", paper.Id),
                  new MySqlParameter("@UpdateTime", paper.UpdateTime),
                new MySqlParameter("@TesQuestiontList",paper.TesQuestiontList),
                  new MySqlParameter("@Level",paper.Level)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        public static bool DeleteTestPaper(int ID)
        {
            string sql = "delete from paper WHERE Id = @ID";
            MySqlParameter[] parms = {
                new MySqlParameter("@ID", ID)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        public static PaperModel GetTestPaperById(int testPaperId)
        {
            PaperModel info = new PaperModel();

            string sql = "select * from paper where Id = " + testPaperId;

            DataSet ds = DbHelper.ExecuteDataset(sql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                info.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
                info.Describes = dt.Rows[0]["Describes"].ToString();
                info.TesQuestiontList = dt.Rows[0]["TesQuestiontList"].ToString();
                info.Level = dt.Rows[0]["Level"].ToString();
                info.TotalScore = decimal.Parse(dt.Rows[0]["TotalScore"].ToString());
                if (!(dt.Rows[0]["CreateTime"] is DBNull))
                    info.CreateTime = DateTime.Parse(dt.Rows[0]["CreateTime"].ToString());
                if (!(dt.Rows[0]["UpdateTime"] is DBNull))
                    info.UpdateTime = DateTime.Parse(dt.Rows[0]["UpdateTime"].ToString());
            }

            info.TestQuestions = PaperTestQuestionDAL.GetQuestionByTestPaperID(testPaperId);

            return info;
        }
        public static List<PaperModel> GetTestPaperByLevel(string paperLevel)
        {
            List<PaperModel> lst = new List<PaperModel>();
            string a = "\"";
            string sql = string.Format("select * from paper where Level ={1}{0}{2}", paperLevel,a,a);
            if(paperLevel=="")
            {
                sql = string.Format("select * from paper where Level is null or Level ={1}{0}{2}", "无", a, a);
            }

            DataSet ds = DbHelper.ExecuteDataset(sql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    PaperModel info = new PaperModel();
                    info.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
                    info.Describes = dt.Rows[i]["Describes"].ToString();
                    info.TesQuestiontList = dt.Rows[i]["TesQuestiontList"].ToString();
                    info.Level = dt.Rows[i]["Level"].ToString();
                    info.TotalScore = decimal.Parse(dt.Rows[i]["TotalScore"].ToString());
                    if (!(dt.Rows[i]["CreateTime"] is DBNull))
                        info.CreateTime = DateTime.Parse(dt.Rows[i]["CreateTime"].ToString());
                    if (!(dt.Rows[i]["UpdateTime"] is DBNull))
                        info.UpdateTime = DateTime.Parse(dt.Rows[i]["UpdateTime"].ToString());
                    lst.Add(info);
                }
            }

            //info.TestQuestions = TestQuestionDAL.GetQuestionByTestPaperID(testPaperId);

            return lst;
        }
    }
}
